﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetAllTaskBySomeone]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetAllTaskBySomeone];
GO
CREATE PROCEDURE [dbo].[sproc_GetAllTaskBySomeone]
    @Date datetime,
             @Username nvarchar(20),
    @Type int   
--@Type = 1 截止到今日待完成记录
--@Type = 2 截止到今日未完成记录
--@Type = 3 所有完成记录
--@Type = 4 获取我的订阅任务
--@Type = 5 获取我的发起
As

IF @Type = 1
BEGIN
SELECT   a.taskID as ID,a.taskID,a.Subject,a.CooperatorList,a.Date,a.IsConfirm ,a.ArrangedBy,a.Type,a.ProjectID,MIN(a.PeriodID) beginPeriodID,MAX(a.PeriodID) endPeriodID,a.EndTime 
    FROM (
        SELECT   UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
            FROM UDS_Schedule,UDS_Task,UDS_Work_Time_Period
            WHERE (Username=@Username) and [date]>=@Date and UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID and isConfirm!=2  
        ) a
    GROUP BY  a.TaskID,a.Subject ,a.CooperatorList,a.IsConfirm ,a.Date,a.Endtime,a.ArrangedBy,a.Type,a.ProjectID ORDER BY  a.EndTime ASC
END

IF @Type = 2
BEGIN
SELECT   a.taskID as ID,a.taskID,a.Subject,a.CooperatorList,a.Date,a.IsConfirm ,a.ArrangedBy,a.Type,a.ProjectID,MIN(a.PeriodID) beginPeriodID,MAX(a.PeriodID) endPeriodID,a.EndTime 
    FROM (
        SELECT   UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
            FROM UDS_Schedule,UDS_Task,UDS_Work_Time_Period
            WHERE  (Username=@Username or ArrangedBy=@Username) and [date]<@Date and UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID and  isConfirm!=2  
        ) a 
    GROUP BY a.TaskID,a.Subject ,a.CooperatorList,a.IsConfirm,a.Date,a.EndTime,a.ArrangedBy,a.Type,a.ProjectID ORDER BY  a.EndTime DESC
END

IF @Type = 3
BEGIN
SELECT  a.taskID as ID,a.taskID,a.Subject,a.CooperatorList,a.Date,a.IsConfirm ,a.ArrangedBy,a.Type,a.ProjectID,MIN(a.PeriodID) beginPeriodID,MAX(a.PeriodID) endPeriodID,a.EndTime 
    FROM (
        SELECT   UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
            FROM UDS_Schedule,UDS_Task,UDS_Work_Time_Period
            WHERE  (Username=@Username or ArrangedBy=@Username)  and UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID and  isConfirm=2  
        ) a 
    GROUP BY a.TaskID,a.Subject ,a.CooperatorList,a.IsConfirm,a.Date,a.EndTime,a.ArrangedBy,a.Type,a.ProjectID ORDER BY a.EndTime DESC
END

--拿出某人订阅任务，从多条记录中取出某中一条
--1.拿出自己订阅任务 task_subscription
--2.根据自己的订阅任务拿出所有执行人的任务
--3.从多条执行人的任务中拿出一条
--4.再对这两条任务进行分组，排序

IF @Type = 4
BEGIN
SELECT   a.taskID as ID,a.taskID,a.Subject,a.CooperatorList,a.Date,a.IsConfirm ,a.ArrangedBy,a.Type,a.ProjectID,MIN(a.PeriodID) beginPeriodID,MAX(a.PeriodID) endPeriodID,a.EndTime 
    FROM(
        SELECT * 
            FROM (
                SELECT  UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
                    FROM UDS_Schedule,
                        (SELECT uds_task.* FROM uds_task,uds_task_subscription WHERE uds_task_subscription.Username=@Username and uds_task_subscription.TaskID=uds_task.ID) UDS_Task,UDS_Work_Time_Period
                            WHERE  UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID) aa
                    WHERE aa.id = (SELECT MAX(ID) FROM 
                        (Select  UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
                    FROM UDS_Schedule,
                        (SELECT uds_task.* FROM uds_task,uds_task_subscription WHERE uds_task_subscription.Username=@Username and uds_task_subscription.TaskID=uds_task.ID) UDS_Task,UDS_Work_Time_Period
                        WHERE  UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID)  bb
                     WHERE aa.TaskID = bb.TaskID)
        ) a GROUP BY a.TaskID,a.Subject ,a.CooperatorList,a.IsConfirm,a.Date,a.EndTime,a.ArrangedBy,a.Type,a.ProjectID ORDER BY a.EndTime DESC

END


IF @Type = 5
BEGIN
SELECT   a.taskID as ID,a.taskID,a.Subject,a.CooperatorList,a.Date,a.IsConfirm ,a.ArrangedBy,a.Type,a.ProjectID,MIN(a.PeriodID) beginPeriodID,MAX(a.PeriodID) endPeriodID,a.EndTime 
    FROM (
        SELECT   UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
            FROM UDS_Schedule,UDS_Task,UDS_Work_Time_Period
            WHERE (ArrangedBy=@Username) and Username!=@Username  and UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID and isConfirm!=2  
        ) a
    GROUP BY  a.TaskID,a.Subject ,a.CooperatorList,a.IsConfirm ,a.Date,a.Endtime,a.ArrangedBy,a.Type,a.ProjectID ORDER BY  a.EndTime ASC

END